﻿using System;
using System.Collections.Generic;
using System.Text;
using Mono.Data.Sqlite;
using System.Data;


namespace Rebides
{
    class Program
    {
        static int year = 10;
        static object defaultValue = 0;
        static Database database = new Database("rebides.db");
        static IDataReader reader;
        static List<int> results = new List<int>();
        static List<String> listaDegree = new List<String>();
        static List<String> listaEstabelecimento = new List<String>();
        static List<teacherPerEstablishmentPerDegree> listTeacherPerEstablishmentPerDegree = new List<teacherPerEstablishmentPerDegree>();
        static System.IO.StreamWriter file = new System.IO.StreamWriter("rebides.txt");
        static List<setTeacherChangeEstablishment> listFirstYear = new List<setTeacherChangeEstablishment>();
        static List<setTeacherChangeEstablishment> listLastYear = new List<setTeacherChangeEstablishment>();
        static List<finalSetTeacherChangeEstablishment> listFinal = new List<finalSetTeacherChangeEstablishment>();

        public struct teacherPerEstablishmentPerDegree
        {
            public int ano { get; set; }
            public string establishment { get; set; }
            public string degree { get; set; }
            public object count { get; set; }
        }
        public struct setTeacherPerEstablishmentPerDegree
        {
            public int ano { get; set; }
            public string establishment { get; set; }
            public string teacher { get; set; }
        }

        public struct teacherPromotedCategory
        {
            public int ano { get; set; }
            public string establishment { get; set; }
            public string teacher { get; set; }
            public string category { get; set; }
        }

        public struct setTeacherChangeEstablishment
        {
            public int ano { get; set; }
            public string establishment { get; set; }
            public string teacher { get; set; }
        }

        public struct finalSetTeacherChangeEstablishment
        {
            public int ano { get; set; }
            public string oldEstablishment { get; set; }
            public string newEstablishment { get; set; }
            public string teacher { get; set; }
        }

        public struct Establishment
        {
            public int ano { get; set; }
            public string establishment { get; set; }
        }

        public struct Teacher
        {
            public int ano { get; set; }
            public string teacher { get; set; }
        }

        public struct teacherPerEstablishment
        {
            public int ano {get; set;}
            public string establishment { get; set; }
            public object count { get; set; }
        }
        public struct teacherPerDegree
        {
            public int ano { get; set; }
            public string degree { get; set; }
            public object count { get; set; }
        }

        static void openDatabase()
        {
            database.OpenDatabase();
        }
        static void Main(string[] args)
        {
            openDatabase();
            loadToLists();
            getTeacherHigherEducation();
            getTeacherPerEstablishmentPerYear();
            getTeacherPerDegreePerYear();
            getTeacherPerEstablishmentPerDegreePerYear();
            getTeacherPerEstablishmentPerDoctoratePerYear();
            getSetTeacherPerEstablishmentPerDoctoratePerYear();
            getEstablishmentPerYear();
            getHolderDegree();
            getListData();
            getPersonnelLeaving();
            getNewPersonnel();
            getNumberOfTeacherMigrating();
            GetSetTeachersChangedEstablishmentPerYear();
            getTeacherPromoted();
            file.Close();
            database.CloseDatabase();           
        }

        static void loadToLists()
        {
            reader = database.ExecuteQuery("SELECT designacao FROM estabelecimentos");
            while (reader.Read())
            {
                listaEstabelecimento.Add(reader.GetString(0));
            }
            database.CloseReader();

            reader = database.ExecuteQuery("SELECT designacao FROM graus");
            while (reader.Read())
            {
                listaDegree.Add(reader.GetString(0));
            }
            database.CloseReader();
            
        }

        static void getTeacherHigherEducation()
        {
            Dictionary<int, object> dictionaryTeachersPerYear = new Dictionary<int, object>();
            for (int i = 0; i < year; i++)
            {
                reader = database.ExecuteQuery(string.Format("SELECT COUNT (DISTINCT docente) FROM fichas_docencia WHERE ano = {0}", i));
                dictionaryTeachersPerYear.Add(i, reader.GetValue(0));
            }
            database.CloseReader();
            for (int i = 0; i < year; i++)
            {
                file.Write("Year = 200{0}", i);
                file.WriteLine(" Number of Teachers = " + dictionaryTeachersPerYear[i]);
                
            }
            database.CloseReader();
            
        }

        static void getTeacherPerEstablishmentPerYear()
        {
            List<teacherPerEstablishment> listTeacherPerEstablishment = new List<teacherPerEstablishment>();
            for (int i = 0; i < year; i++)
            {
                foreach (string e in listaEstabelecimento)
                {
                    reader = database.ExecuteQuery(string.Format("SELECT COUNT (DISTINCT docente) FROM fichas_docencia WHERE ano = {0} AND estabelecimento = '{1}'", i,e));
                    while (reader.Read())
                    {
                            listTeacherPerEstablishment.Add(new teacherPerEstablishment { ano = i, establishment = e, count = reader.GetValue(0) });
                    }
                }
            }
            database.CloseReader();
            listTeacherPerEstablishment.Sort(delegate(teacherPerEstablishment e1, teacherPerEstablishment e2)
                {
                    return Comparer<string>.Default.Compare(e1.establishment, e2.establishment);
                });
            file.WriteLine("");
            file.WriteLine("---Number of teachers Per Establishment Per Year---");
            foreach (teacherPerEstablishment tpe in listTeacherPerEstablishment)
            {
                if (tpe.count != defaultValue)
                {
                    file.WriteLine("Ano = 200" + tpe.ano + " Faculdade = " + tpe.establishment + " Número de Professores = " + tpe.count);
                }
            }
        }
        static void getTeacherPerDegreePerYear()
        {
            List<teacherPerDegree> listTeacherPerDegree = new List<teacherPerDegree>();
            for (int i = 0; i < year; i++)
            {
                foreach (string d in listaDegree)
                {
                    reader = database.ExecuteQuery(string.Format("SELECT COUNT (DISTINCT docente) FROM fichas_docencia WHERE ano = {0} AND grau = '{1}'", i, d));
                    while (reader.Read())
                    {
                            listTeacherPerDegree.Add(new teacherPerDegree { ano = i, degree = d, count = reader.GetValue(0) });
                    }
                }
            }
            database.CloseReader();
            foreach (teacherPerDegree tpe in listTeacherPerDegree)
            {
               file.WriteLine("Ano = 200" + tpe.ano + " Grau = " + tpe.degree + " Número de Professores = " + tpe.count);
            }
        }
        static void getTeacherPerEstablishmentPerDegreePerYear()
        {
            for (int i = 0; i < year; i++)
            {
                foreach (string e in listaEstabelecimento)
                {
                    foreach (string d in listaDegree)
                    {
                        reader = database.ExecuteQuery(string.Format("SELECT COUNT (DISTINCT docente) FROM fichas_docencia WHERE ano = {0} AND estabelecimento = '{1}' AND grau = '{2}'", i, e,d));
                        while (reader.Read())
                        {
                            if (reader.GetValue(0) != defaultValue)
                            {
                                listTeacherPerEstablishmentPerDegree.Add(new teacherPerEstablishmentPerDegree { ano = i, establishment = e, degree = d, count = reader.GetValue(0) });
                            }
                        }
                    }
                }
            }
            listTeacherPerEstablishmentPerDegree.Sort(delegate(teacherPerEstablishmentPerDegree tpepd1, teacherPerEstablishmentPerDegree tpepd2)
            {
                return Comparer<string>.Default.Compare(tpepd1.establishment, tpepd2.establishment);
            });
            listTeacherPerEstablishmentPerDegree.Sort(delegate(teacherPerEstablishmentPerDegree tpepd1, teacherPerEstablishmentPerDegree tpepd2)
            {
                return Comparer<string>.Default.Compare(tpepd1.degree, tpepd2.degree);
            });
            database.CloseReader();

            file.WriteLine("");
            file.WriteLine("---Number of teachers Per Establishment Per Degree Per Year---");

            foreach (teacherPerEstablishmentPerDegree tpepd in listTeacherPerEstablishmentPerDegree)
            {
                if (listTeacherPerEstablishmentPerDegree.IndexOf(tpepd) == 0)
                {
                    file.WriteLine("Ano = 200" + tpepd.ano + " Faculdade = " + tpepd.establishment + " Número de Professores = " + tpepd.count);
                    continue;
                }
                else
                {
                    if (tpepd.establishment == listTeacherPerEstablishmentPerDegree[listTeacherPerEstablishmentPerDegree.IndexOf(tpepd) - 1].establishment)
                    {
                        file.WriteLine("Grau = 200" + tpepd.degree + " Número de Professores = " + tpepd.count);
                    }
                        
                    else
                    {
                        file.WriteLine("Ano = 200" + tpepd.ano + " Faculdade = " + tpepd.establishment + " Grau = " + tpepd.degree + " Número de Professores = " + tpepd.count);
                    }
                }
            }
        }
        static void getTeacherPerEstablishmentPerDoctoratePerYear()
        {
            List<teacherPerEstablishment> listTeacherPerEstablishment = new List<teacherPerEstablishment>();
            for (int i = 0; i < year; i++)
            {
                foreach (string e in listaEstabelecimento)
                {
                    reader = database.ExecuteQuery(string.Format("SELECT COUNT (DISTINCT docente) FROM fichas_docencia WHERE ano = {0} AND estabelecimento = '{1}' AND grau = 'Doutoramento'", i, e));
                    while (reader.Read())
                    {
                        listTeacherPerEstablishment.Add(new teacherPerEstablishment { ano = i, establishment = e, count = reader.GetValue(0) });
                    }
                }
            }
            database.CloseReader();
            listTeacherPerEstablishment.Sort(delegate(teacherPerEstablishment tpe1, teacherPerEstablishment tpe2)
            {
                return Comparer<string>.Default.Compare(tpe1.establishment, tpe2.establishment);
            });

            file.WriteLine("");
            file.WriteLine("---Number of Teachers with Doctorate degree Per Establishment Per Year---");

            foreach (teacherPerEstablishment tpe in listTeacherPerEstablishment)
            {
                if (tpe.count != defaultValue)
                {
                    file.WriteLine("Ano = 200" + tpe.ano + " Faculdade = " + tpe.establishment + " Número de Professores com Doutoramento = " + tpe.count);
                }
            }


        }
        static void getSetTeacherPerEstablishmentPerDoctoratePerYear()
        {
            List<setTeacherPerEstablishmentPerDegree> listSetTeacherPerEstablishment = new List<setTeacherPerEstablishmentPerDegree>();
            for (int i = 0; i < year; i++)
            {
                foreach (string e in listaEstabelecimento)
                {
                        reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente FROM fichas_docencia WHERE ano = {0} AND estabelecimento = '{1}' AND grau = 'Doutoramento'", i, e));
                        while (reader.Read())
                        {
                            listSetTeacherPerEstablishment.Add(new setTeacherPerEstablishmentPerDegree { ano = i, establishment = e, teacher = reader.GetString(0) });
                        }
                }
            }
            database.CloseReader();
            listSetTeacherPerEstablishment.Sort(delegate(setTeacherPerEstablishmentPerDegree s1, setTeacherPerEstablishmentPerDegree s2)
            {
                return Comparer<string>.Default.Compare(s1.establishment, s2.establishment);
            });
            listSetTeacherPerEstablishment.Sort(delegate(setTeacherPerEstablishmentPerDegree s1, setTeacherPerEstablishmentPerDegree s2)
            {
                return Comparer<string>.Default.Compare(s1.teacher, s2.teacher);
            });

            file.WriteLine("");
            file.WriteLine("---Set of Teachers with Doctorate degree Per Establishment Per Year---");

             foreach (setTeacherPerEstablishmentPerDegree set in listSetTeacherPerEstablishment)
            {
                if (listSetTeacherPerEstablishment.IndexOf(set) == 0)
                {
                    file.WriteLine("Ano = 200" + set.ano + " Faculdade = " + set.establishment);
                    file.WriteLine("Nome do Professores = " + set.teacher);
                    continue;
                }
                else
                {
                    if (set.establishment == listSetTeacherPerEstablishment[listSetTeacherPerEstablishment.IndexOf(set) - 1].establishment)
                    {
                        file.WriteLine("Nome do Professor = " + set.teacher);
                    }
                        
                    else
                    {
                        file.WriteLine("Ano = 200" + set.ano + " Faculdade = " + set.establishment);
                        file.WriteLine("Nome do Professores = " + set.teacher);
                    }
                }
            }
        }

        //Lists

        static void getEstablishmentPerYear()
        {
            List<Establishment> listEstablishmentPerYear = new List<Establishment>();
            for (int i = 0; i < year; i++)
            {
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT estabelecimento FROM fichas_docencia WHERE ano = {0}", i));
                while (reader.Read())
                {
                    listEstablishmentPerYear.Add(new Establishment { ano = i, establishment = reader.GetString(0) });
                }
            }
            database.CloseReader();
            listEstablishmentPerYear.Sort(delegate(Establishment e1, Establishment e2)
            {
                return Comparer<string>.Default.Compare(e1.establishment, e2.establishment);
            });
            file.WriteLine("");
            file.WriteLine("---List of Establishments Per Year---");
            foreach (Establishment epe in listEstablishmentPerYear)
            {
                file.WriteLine("Ano = 200" + epe.ano + " Faculdade = " + epe.establishment);
            }
        }

        static void getHolderDegree()
        {
            List<Teacher> listHolderDegree = new List<Teacher>();
            for (int i = 0; i < year; i++)
            {
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente FROM fichas_docencia WHERE ano = {0} and grau = 'Licenciatura'", i));
                while (reader.Read())
                {
                    listHolderDegree.Add(new Teacher { ano = i, teacher = reader.GetString(0) });
                }
            }
            listHolderDegree.Sort(delegate(Teacher t1, Teacher t2)
            {
                return Comparer<string>.Default.Compare(t1.teacher, t2.teacher);
            });
            database.CloseReader();
            file.WriteLine("");
            file.WriteLine("---List of Holders of a Degree Per Year---");
            foreach (Teacher t in listHolderDegree)
            {
                file.WriteLine("Ano = 200" + t.ano + " Nome do Professor = " + t.teacher);
            }
        }

        //Method to load data to make set of teachers changing establishment, personnel leaving and new personnel
        static void getListData()
        {
            for (int i = 0; i < year; i++)
            {
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente, estabelecimento FROM fichas_docencia WHERE ano = {0}", i - 1));
                while (reader.Read())
                {
                    listFirstYear.Add(new setTeacherChangeEstablishment { ano = i, teacher = reader.GetString(0), establishment = reader.GetString(1) });
                }
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente, estabelecimento FROM fichas_docencia WHERE ano = {0}", i));
                while (reader.Read())
                {
                    listLastYear.Add(new setTeacherChangeEstablishment { ano = i, teacher = reader.GetString(0), establishment = reader.GetString(1) });
                }
                for (int x = 0; x < listFirstYear.Count - 1; x++)
                {
                    setTeacherChangeEstablishment resultTeacher = listLastYear.Find(delegate(setTeacherChangeEstablishment th)
                    {
                        return th.teacher == listFirstYear[x].teacher;
                    });
                    if (resultTeacher.teacher != null)
                    {
                        if (resultTeacher.establishment != listFirstYear[x].establishment)
                        {
                            listFinal.Add(new finalSetTeacherChangeEstablishment { ano = i, teacher = resultTeacher.teacher, oldEstablishment = listFirstYear[x].establishment, newEstablishment = resultTeacher.establishment });
                        }
                    }
                }
                listFirstYear.Clear();
                listLastYear.Clear();
            }
            database.CloseReader();
        }

        static void GetSetTeachersChangedEstablishmentPerYear()
        {
            listFinal.Sort(delegate(finalSetTeacherChangeEstablishment t1, finalSetTeacherChangeEstablishment t2)
            {
                return Comparer<string>.Default.Compare(t1.newEstablishment, t2.newEstablishment);
            });
            file.WriteLine("");
            file.WriteLine("---Set of teachers that changed from one establishment to another one per year---");
            foreach (finalSetTeacherChangeEstablishment t in listFinal)
            {
                file.WriteLine("Ano = 200" + t.ano + " Nome do Professor = " + t.teacher+ " Novo Estabelecimento = " + t.newEstablishment + " Antigo Estabelecimento ="+ t.oldEstablishment);
            }
        }


        static void getPersonnelLeaving()
        {
            listFinal.Sort(delegate(finalSetTeacherChangeEstablishment t1, finalSetTeacherChangeEstablishment t2)
            {
                return Comparer<string>.Default.Compare(t1.oldEstablishment, t2.oldEstablishment);
            });
            file.WriteLine("");
            file.WriteLine("---Personnel leaving the institution per year---");
            foreach (finalSetTeacherChangeEstablishment t in listFinal)
            {
                file.WriteLine("Ano = 200" + t.ano + " Nome do Professor = " + t.teacher + " Antigo Estabelecimento = " + t.oldEstablishment);
            }
        }
        static void getNewPersonnel()
        {
            listFinal.Sort(delegate(finalSetTeacherChangeEstablishment t1, finalSetTeacherChangeEstablishment t2)
            {
                return Comparer<string>.Default.Compare(t1.newEstablishment, t2.newEstablishment);
            });

            file.WriteLine("");
            file.WriteLine("---New personnel in the institution per year---");

            foreach (finalSetTeacherChangeEstablishment t in listFinal)
            {
                file.WriteLine("Ano = 200" + t.ano + " Nome do Professor = " + t.teacher + " Novo Estabelecimento = " + t.newEstablishment);
            }
        }

        static void getNumberOfTeacherMigrating()
        {
            List<teacherPerEstablishment> listMigrating = new List<teacherPerEstablishment>();
            List<finalSetTeacherChangeEstablishment> resultTeacher = new List<finalSetTeacherChangeEstablishment>();
            List<setTeacherChangeEstablishment> temporaryResult = new List<setTeacherChangeEstablishment>();

            file.WriteLine("");
            file.WriteLine("---Number of teachers migrating from one establishment to another---");
            file.WriteLine("");
                foreach (string e in listaEstabelecimento)
                {
                    resultTeacher = listFinal.FindAll(delegate(finalSetTeacherChangeEstablishment th)
                {
                    return th.newEstablishment == e;
                });
                    listMigrating.Add(new teacherPerEstablishment { ano = 0, establishment = e, count = resultTeacher.Count });
                    resultTeacher.Clear();
                }
            
            listMigrating.Sort(delegate(teacherPerEstablishment t1, teacherPerEstablishment t2)
            {
                return Comparer<string>.Default.Compare(t1.establishment, t2.establishment);
            });
            foreach (teacherPerEstablishment tpe in listMigrating)
            {
                if (tpe.count.ToString() != defaultValue.ToString())
                {
                    file.WriteLine("Estabelecimento = " + tpe.establishment + "- Número que migrou = " + tpe.count.ToString());
                }
            }
        }
        static void getTeacherPromoted()
        {
            List<teacherPromotedCategory> firstYearlist = new List<teacherPromotedCategory>();
            List<teacherPromotedCategory> lastYearList = new List<teacherPromotedCategory>();
            List<teacherPerEstablishment> completedList = new List<teacherPerEstablishment>();
            for (int i = 0; i < year; i++)
            {
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente, estabelecimento, categoria FROM fichas_docencia WHERE ano = {0}", i - 1));
                while (reader.Read())
                {
                    firstYearlist.Add(new teacherPromotedCategory { ano = i, teacher = reader.GetString(0), establishment = reader.GetString(1), category = reader.GetString(2) });
                }
                reader = database.ExecuteQuery(string.Format("SELECT DISTINCT docente, estabelecimento, categoria FROM fichas_docencia WHERE ano = {0}", i));
                while (reader.Read())
                {
                    lastYearList.Add(new teacherPromotedCategory { ano = i, teacher = reader.GetString(0), establishment = reader.GetString(1), category = reader.GetString(2) });
                }
                for (int x = 0; x < firstYearlist.Count - 1; x++)
                {
                    teacherPromotedCategory lastList = firstYearlist.Find(delegate(teacherPromotedCategory th)
                    {
                        return th.teacher == firstYearlist[x].teacher;
                    });
                    if (lastList.teacher != null)
                    {
                        if (lastList.category != firstYearlist[x].category)
                        {
                            completedList.Add(new teacherPerEstablishment { ano = i, establishment = lastList.establishment });
                        }
                    }
                }
                firstYearlist.Clear();
                lastYearList.Clear();
            }

            List<teacherPerEstablishment> listMigrating = new List<teacherPerEstablishment>();
            List<teacherPerEstablishment> resultTeacher = new List<teacherPerEstablishment>();
            List<teacherPerEstablishment> temporaryResult = new List<teacherPerEstablishment>();

            file.WriteLine("");
            file.WriteLine("---Number of teachers promoted to the next category each year per establishment;---");
            file.WriteLine("");
                foreach (string e in listaEstabelecimento)
                {
                    resultTeacher = completedList.FindAll(delegate(teacherPerEstablishment th)
                {
                    return th.establishment == e;
                });
                    for (int i = 0; i < year; i++)
                    {
                        temporaryResult = resultTeacher.FindAll(delegate(teacherPerEstablishment th)
                        {
                            return th.ano == i;
                        });
                        listMigrating.Add(new teacherPerEstablishment { ano = i, establishment = e, count = temporaryResult.Count });
                    }
                    resultTeacher.Clear();
                }
            
            listMigrating.Sort(delegate(teacherPerEstablishment t1, teacherPerEstablishment t2)
            {
                return Comparer<string>.Default.Compare(t1.establishment, t2.establishment);
            });
            foreach (teacherPerEstablishment tpe in listMigrating)
            {
                if (tpe.count.ToString() != defaultValue.ToString())
                {
                    file.WriteLine("Ano = 200" + tpe.ano + "- Estabelecimento = " + tpe.establishment + "- Número de promovidos = " + tpe.count.ToString());
                }
            }
        }
    }
}
